package com.fub.guidance;

import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;

import com.fub.util.database.databaseHelper.EdgesDatabaseManager;
import com.fub.util.database.databaseHelper.NodesDatabaseManager;
import com.fub.util.database.databaseHelper.RelationDatabaseManager;

public class RoutingGraphDAO {

	private static final String SELECT_STATEMENT = "SELECT * FROM %s";
	private NodesDatabaseManager nodesDatabaseManager;
	private EdgesDatabaseManager edgesDatabaseManager;
	private RelationDatabaseManager relationDatabaseManager;

	/**
	 * Constructs a new data access object for routing data.
	 * 
	 * @param context
	 *            the application context
	 */
	public RoutingGraphDAO(Context context) {
		this.nodesDatabaseManager = new NodesDatabaseManager(context);
		this.edgesDatabaseManager = new EdgesDatabaseManager(context);
		this.relationDatabaseManager = new RelationDatabaseManager(context);
	}

	/**
	 * Returns all relations from database.
	 * 
	 * @param builingId
	 *            the id of the building
	 * @return all relations from database
	 */
	public List<Relation> getAllRelations(String builingId) {
		List<Relation> result = new ArrayList<Relation>();
		String query = String.format(
				"SELECT DISTINCT %s FROM %s WHERE %s = '%s' ",
				RelationDatabaseManager.COL_RELATION_ID,
				RelationDatabaseManager.TABLE_NAME_RELATIONS,
				RelationDatabaseManager.COL_BUILDING_ID, builingId);
		Cursor cursor = this.relationDatabaseManager.getReadableDatabase().rawQuery(
				query, null);
		while (cursor.moveToNext()) {
			String id = cursor.getString(0);
			Relation relation = getRelationById(id);
			result.add(relation);
		}
		cursor.close();
		return result;
	}

	/**
	 * Returns a relation from database.
	 * 
	 * @param id
	 *            the id of the relation
	 * @return a relation from database
	 */
	public Relation getRelationById(String id) {
		String query = String.format("SELECT * FROM %s WHERE %s = '%s'",
				RelationDatabaseManager.TABLE_NAME_RELATIONS,
				RelationDatabaseManager.COL_RELATION_ID, id);
		Cursor relationCursor = this.relationDatabaseManager.getReadableDatabase()
				.rawQuery(query, null);
		Relation relation = new Relation();
		List<String> nodes = new ArrayList<String>();
		if (relationCursor.moveToNext()) {
			relation.setBuildingID(relationCursor.getString(0));
			relation.setId(relationCursor.getString(1));
			relation.setType(relationCursor.getString(2));
			nodes.add(relationCursor.getString(3));
			while (relationCursor.moveToNext()) {
				nodes.add(relationCursor.getString(3));
			}
		}
		relation.setNodes(nodes);
		relationCursor.close();
		return relation;
	}

	/**
	 * Returns a relation from database for a given type.
	 * 
	 * @param type
	 *            the type of the relation
	 * @return a relation from database
	 */
	public Relation getRelation(String type) {
		String query = String.format("SELECT * FROM %s WHERE %s = '%s'",
				RelationDatabaseManager.TABLE_NAME_RELATIONS,
				RelationDatabaseManager.COL_TYPE, type);
		Cursor relationCursor = this.relationDatabaseManager.getReadableDatabase()
				.rawQuery(query, null);
		Relation relation = new Relation();
		List<String> nodes = new ArrayList<String>();
		if (relationCursor.moveToNext()) {
			relation.setBuildingID(relationCursor.getString(0));
			relation.setId(relationCursor.getString(1));
			relation.setType(relationCursor.getString(2));
			nodes.add(relationCursor.getString(3));
			while (relationCursor.moveToNext()) {
				nodes.add(relationCursor.getString(3));
			}
		}
		relation.setNodes(nodes);
		relationCursor.close();
		return relation;
	}

	/**
	 * Inserts a relation into database.
	 * 
	 * @param relation
	 *            the relation to be insert
	 */
	public void insertRelation(Relation relation) {
		for (String nodeId : relation.getNodes()) {
			String query = String.format(
					"INSERT INTO %s VALUES ('%s','%s','%s','%s')",
					RelationDatabaseManager.TABLE_NAME_RELATIONS,
					relation.getBuildingID(), relation.getId(),
					relation.getType(), nodeId);
			this.relationDatabaseManager.getWritableDatabase().execSQL(query);
		}
	}

	/**
	 * Inserts a node into database.
	 * 
	 * @param node
	 *            the node to be insert
	 */
	public void insertNode(Vertex node) {
		String query = String.format(
				"INSERT INTO %s  VALUES ('%s','%s','%s','%d','%d','%d','%s')",
				NodesDatabaseManager.TABLE_NAME_NODES, node.getId(), node
						.getBuildingId(), node.getType().toString(), node
						.getPosition().getX(), node.getPosition().getY(), node
						.getPosition().getZ(), node.getLabel());
		this.nodesDatabaseManager.getWritableDatabase().execSQL(query);
	}

	/**
	 * Inserts a edge into database.
	 * 
	 * @param node
	 *            the node to be insert
	 */
	public void insertEdge(Edge edge) {
		String query = String.format(
				"INSERT INTO %s (%s,%s,%s) VALUES ('%s','%s','%s')",
				EdgesDatabaseManager.TABLE_NAME_EDGES,
				EdgesDatabaseManager.BUILDING_ID_COL,
				EdgesDatabaseManager.COL_FROM, EdgesDatabaseManager.COL_TO,
				edge.getBuildingId(), edge.getSourceId(),
				edge.getDestinationId());
		this.edgesDatabaseManager.getWritableDatabase().execSQL(query);
	}

	/**
	 * Returns all edges from database for a given building id.
	 * 
	 * @param buildingId
	 *            the id of the building
	 * @return all edges from database
	 */
	public List<Edge> getEdges(String buildingId) {
		List<Edge> result = new ArrayList<Edge>();
		String query = String.format("SELECT * FROM %s WHERE %s = '%s'",
				EdgesDatabaseManager.TABLE_NAME_EDGES,
				EdgesDatabaseManager.BUILDING_ID_COL, buildingId);

		Cursor edgeCursor = this.edgesDatabaseManager.getReadableDatabase()
				.rawQuery(query, null);
		while (edgeCursor.moveToNext()) {
			String id = "e" + edgeCursor.getInt(0);
			String bId = edgeCursor.getString(1);
			String from = edgeCursor.getString(2);
			String to = edgeCursor.getString(3);
			Edge edge = new Edge(id, bId, from, to, -1);
			result.add(edge);
		}
		edgeCursor.close();
		return result;
	}

	/**
	 * Returns all edges from database.
	 * 
	 * @return all edges from database
	 */
	public List<Edge> getAllEdges() {
		List<Edge> result = new ArrayList<Edge>();
		Cursor edgeCursor = this.edgesDatabaseManager.getReadableDatabase()
				.rawQuery(
						String.format(SELECT_STATEMENT,
								EdgesDatabaseManager.TABLE_NAME_EDGES), null);
		while (edgeCursor.moveToNext()) {
			String id = "e" + edgeCursor.getInt(0);
			String buildingId = edgeCursor.getString(1);
			String from = edgeCursor.getString(2);
			String to = edgeCursor.getString(3);
			Edge edge = new Edge(id, buildingId, from, to, 0);
			result.add(edge);
		}
		edgeCursor.close();
		return result;
	}

	/**
	 * Returns all nodes for a given building id.
	 * 
	 * @param buildingId
	 *            the id of the building
	 * @return all nodes for a given building id
	 */
	public List<Vertex> getNodes(String buildingId) {
		List<Vertex> result = new ArrayList<Vertex>();

		String query = String.format("SELECT * FROM %s WHERE %s = '%s'",
				NodesDatabaseManager.TABLE_NAME_NODES,
				NodesDatabaseManager.COL_BUILDING_ID, buildingId);
		Cursor nodeCursor = this.nodesDatabaseManager.getReadableDatabase()
				.rawQuery(query, null);
		while (nodeCursor.moveToNext()) {
			String id = nodeCursor.getString(0);
			String bId = nodeCursor.getString(1);
			String type = nodeCursor.getString(2);
			int x = nodeCursor.getInt(3);
			int y = nodeCursor.getInt(4);
			int z = nodeCursor.getInt(5);
			Point position = new Point(x, y, z);
			String roomLabel = "";
			if (nodeCursor.getColumnCount() == 7) {
				roomLabel = nodeCursor.getString(6);
			} else {
				roomLabel = "No person";
			}
			Vertex vertex = new Vertex(id, bId, position, type, roomLabel);
			result.add(vertex);
		}
		nodeCursor.close();
		return result;
	}

	/**
	 * Returns all nodes from database.
	 * 
	 * @return all nodes from database
	 */
	public List<Vertex> getAllNodes() {
		List<Vertex> result = new ArrayList<Vertex>();
		Cursor nodeCursor = this.nodesDatabaseManager.getReadableDatabase()
				.rawQuery(
						String.format(SELECT_STATEMENT,
								NodesDatabaseManager.TABLE_NAME_NODES), null);
		while (nodeCursor.moveToNext()) {
			String id = nodeCursor.getString(0);
			String buildingId = nodeCursor.getString(1);
			String type = nodeCursor.getString(2);
			int x = nodeCursor.getInt(3);
			int y = nodeCursor.getInt(4);
			int z = nodeCursor.getInt(5);
			Point position = new Point(x, y, z);
			String roomLabel = "";
			if (type.equalsIgnoreCase("room")) {
				roomLabel = nodeCursor.getString(6);
			} else {
				roomLabel = "No person";
			}
			Vertex vertex = new Vertex(id, buildingId, position, type,
					roomLabel);
			result.add(vertex);
		}
		nodeCursor.close();
		return result;
	}

	/**
	 * Deletes all nodes from database.
	 */
	public void deleteAllNodes() {
		String query = String.format("DELETE FROM %s",
				NodesDatabaseManager.TABLE_NAME_NODES);
		this.nodesDatabaseManager.getWritableDatabase().execSQL(query);
	}

	/**
	 * Deletes all edges from database.
	 */
	public void deleteAllEdges() {
		String query = String.format("DELETE FROM %s",
				EdgesDatabaseManager.TABLE_NAME_EDGES);
		this.edgesDatabaseManager.getWritableDatabase().execSQL(query);

	}

	/**
	 * Deletes all relations from database.
	 */
	public void deleteAllRelations() {
		String query = String.format("DELETE FROM %s",
				RelationDatabaseManager.TABLE_NAME_RELATIONS);
		this.relationDatabaseManager.getWritableDatabase().execSQL(query);

	}

	/**
	 * Deletes all nodes for a given building id.
	 * 
	 * @param buildingId
	 */
	public void deleteAllNodes(String buildingId) {
		String query = String.format("DELETE FROM %s WHERE %s = '%s'",
				NodesDatabaseManager.TABLE_NAME_NODES,
				NodesDatabaseManager.COL_BUILDING_ID, buildingId);
		this.nodesDatabaseManager.getWritableDatabase().execSQL(query);
	}

	/**
	 * Deletes all edges for a given building id.
	 * 
	 * @param buildingId
	 *            the id of the building
	 */
	public void deleteAllEdges(String buildingId) {
		String query = String.format("DELETE FROM %s WHERE %s = '%s'",
				EdgesDatabaseManager.TABLE_NAME_EDGES,
				EdgesDatabaseManager.BUILDING_ID_COL, buildingId);
		this.edgesDatabaseManager.getWritableDatabase().execSQL(query);
	}

	/**
	 * Deletes all relations for a given building id.
	 * 
	 * @param buildingId
	 *            the id of the building
	 */
	public void deleteAllRelations(String buildingId) {
		String query = String.format("DELETE FROM %s WHERE %s = '%s'",
				RelationDatabaseManager.TABLE_NAME_RELATIONS,
				RelationDatabaseManager.COL_BUILDING_ID, buildingId);
		this.relationDatabaseManager.getWritableDatabase().execSQL(query);
	}
}
